﻿using System;
using System.Collections.Generic;
using System.Text;

namespace Kalficom.Query
{
    class qry_contract
    {
        internal string get = "SELECT * FROM k_contract";
        internal string get_list = "SELECT cont_id, cont_number, date_start, date_end, TX_CODE, TX_NOM, TX_TEL, active FROM k_contract k INNER JOIN NTIERS_X n ON k.client_to_invoice = n.TX_CODE";
        internal string deleted = "DELETE FROM k_contract WHERE cont_id={0}";
        internal string del_cont_client_taken = "DELETE FROM k_client_taken WHERE cont_id={0}";
        internal string del_cont_taken = "DELETE FROM k_taken WHERE client_taken_id IN (SELECT client_taken_id FROM k_client_taken WHERE cont_id={0})";
        internal string del_cont_taken_analyze = "DELETE FROM k_taken_analyze WHERE taken_id IN (SELECT taken_id FROM k_taken WHERE client_taken_id IN (SELECT client_taken_id FROM k_client_taken WHERE cont_id={0}))";
        internal string scalarID = "SELECT cont_id FROM k_contract WHERE cont_number={0}";
        internal string get_where = "SELECT * FROM k_contract WHERE cont_id={0}";

        //Client Information
        internal string get_client = "SELECT TX_CODE, TX_NOM, TX_ADR1, TX_ADR2, TX_ADR3, TX_CP, TX_VILLE, TX_TEL, TX_MOBILE FROM NTIERS_X ORDER BY TX_CODE";

        //Client Taken form
        internal string get_list_clientTaken = "SELECT client_taken_id, client_taken_code, zone_label, TX_NOM, TX_TEL FROM k_client_taken c"
                                            + " INNER JOIN k_zone_geographic z ON c.zone_id = z.zone_id"
                                            + " INNER JOIN NTIERS_X x ON c.client_taken_code = x.TX_CODE"
                                            + " WHERE cont_id={0}";
        internal string get_zone = "SELECT zone_id, zone_code, zone_label FROM k_zone_geographic";
        internal string get_clientTaken = "SELECT * FROM k_client_taken WHERE client_taken_id={0}";
        internal string get_clientTaken_ID = "SELECT MAX(client_taken_id) FROM k_client_taken";
        internal string deleted_clientTaken = "DELETE FROM k_client_taken WHERE client_taken_id={0}";
        internal string del_clientTaken_Taken = "DELETE FROM k_taken WHERE client_taken_id={0}";
        internal string del_clientTaken_Analyze = "DELETE FROM k_taken_analyze WHERE taken_id IN (SELECT taken_id FROM k_taken WHERE client_taken_id = {0})";
        internal string scalarID_clientTaken = "SELECT client_taken_id FROM k_client_taken WHERE client_taken_code={0} AND zone_id={1} AND cont_id={2}";
        internal string add_copyTaken = "INSERT INTO k_taken SELECT prod_code, taken_type, taken_code, {0} as client_taken_id, surv_code, taken_point_code, taken_point_label,taken_point_modifiable, taken_date, days_taken, months_taken, years_taken, taken_comment, {2}, GETDATE() as date_create, taken_point_day, taken_date_next, taken_date_last, amount FROM k_taken WHERE taken_id = {1}";
        internal string add_copyAnalyse = "INSERT INTO k_taken_analyze SELECT analyze_code, analyze_label, {0} as taken_id, to_do, {2}, GETDATE() as date_create FROM k_taken_analyze WHERE taken_id = {1}";

        //Taken form
        internal string get_taken = "SELECT * FROM k_taken WHERE taken_id={0}";
        internal string get_taken_list = "SELECT [taken_id],[prod_code],[taken_point_code],[taken_point_label],[days_taken],[months_taken],[taken_point_day],[PRD_LIB] {1} FROM [k_taken] INNER JOIN [NPRODUIT] ON [k_taken].[prod_code]=[NPRODUIT].[PRD_CODE] WHERE [client_taken_id]={0} {2}";
        internal string get_takenId = "SELECT MAX(taken_id) FROM k_taken";
        internal string del_taken = "DELETE FROM k_taken WHERE taken_id={0}";
        internal string get_product = "SELECT PRD_CODE, PRD_LIB FROM NPRODUIT ORDER BY PRD_CODE";
        internal string get_plan = "SELECT PC_CODE AS PL_CODE, PC_LIBELLE AS PL_LIBELLE FROM PLANCONT ORDER BY PC_CODE";
        internal string get_sector = "SELECT SE_CODE, SE_LIBELLE FROM SECTEURS ORDER BY SE_CODE";
        internal string get_point_surveillance = "SELECT PS_CODE, PS_LIBELLE FROM PTSURV ORDER BY PS_CODE";
        internal string get_point_taken = "SELECT PP_CODSURV, PP_LIBELLE FROM PTPREL ORDER BY PP_CODSURV";
        internal string get_analy_product = "SELECT DISTINCT A.AN_CODE AS analyze_code, A.AN_LIBELLE AS analyze_label, 'TRUE' AS TO_DO FROM ANAPRD P INNER JOIN ANALYSES A ON P.AS_ANA = A.AN_CODE WHERE P.AS_PROD={0}";
        internal string get_analy_plan = "SELECT DISTINCT A.AN_CODE AS analyze_code, A.AN_LIBELLE AS analyze_label, 'TRUE' AS TO_DO FROM ANAPLAN P INNER JOIN ANALYSES A ON P.AP_CODANA = A.AN_CODE WHERE P.AP_CODPLAN={0}";
        internal string get_analy_sector = "SELECT DISTINCT A.AN_CODE AS analyze_code, A.AN_LIBELLE AS analyze_label, 'FALSE' AS TO_DO FROM ANALYSES A WHERE A.AN_SECTEUR={0}";
        internal string get_analy_taken = "SELECT * FROM k_taken_analyze WHERE taken_id={0}";
        internal string del_analy_taken = "DELETE FROM k_taken_analyze WHERE taken_id={0}";
        internal string get_cont_date = "SELECT date_start FROM k_contract WHERE cont_id={0}";
    }
}
